<?php

namespace app\admin\model;

use app\admin\model\Data as DataModel;
use think\Cache;
use think\db\Where;

class Data
{
    //体测人数
    public static function ticeren($map = "")
    {
        //return 0;
        /*if(cache(json_encode($map))){
            return cache(json_encode($map));
        }*/
//        $count = SportRecord::where($map)->field("id")
//            ->group('memberId')
//            //->cache('gmemgroup',250)
//            ->count();
        //\think\facade\Cache::set(json_encode($map),$count,3600*12);
        //echo $count;

        $count = SportRecord::where($map)
            ->fieldRaw('COUNT(DISTINCT memberId) AS total')
            ->find();
        //echo $count;
        //\think\facade\Cache::set(json_encode($map).'ticerenHege',$count,3600*12);
        return $count['total'] ?? 0;
    }

    //合格体测人数
    public static function ticerenHege($map='')
    {
        //return 0;
        /*if(cache(json_encode($map).'ticerenHege')){
            return cache(json_encode($map));
        }*/
//        $count = SportRecord::where($map)->field("id")
//            ->where('rating','>=','1')
//            ->where('mark',1)
//            ->group('memberId')->count();
        $count = SportRecord::where($map)
            ->where('rating','>=','1')
            ->where('mark',1)
            ->fieldRaw('COUNT(DISTINCT memberId) AS total')
            ->find();
        //echo $count;
        //\think\facade\Cache::set(json_encode($map).'ticerenHege',$count,3600*12);
        return $count['total'] ?? 0;
    }

    public static function avg($map = '')
    {
        /*if(cache(json_encode($map).'avg')){
            return cache(json_encode($map));
        }*/
        $count = SportRecord::where($map)
            ->where('mark',1)
            ->whereNotNull('pingscore')
            ->cache(true,140)
            ->avg('pingscore');
        //\think\facade\Cache::set(json_encode($map).'avg',$count,3600*11.5);
        return (int)$count;
    }

    public static function max($map=""){
        /*if(cache(json_encode($map).'max')){
            return cache(json_encode($map));
        }*/
        $count = SportRecord::where($map)
            ->where('mark',1)
            ->whereNotNull('pingscore')
            ->cache(true,80)
            ->max('pingscore');
        //\think\facade\Cache::set(json_encode($map).'max',$count,3600*11.1);
        return $count;
    }

    public static function min($map=''){
        /* if(cache(json_encode($map).'min')){
             return cache(json_encode($map));
         }*/
        $count = SportRecord::where($map)
            ->where('mark',1)
            ->whereNotNull('pingscore')
            ->cache(true,100)
            ->min('pingscore');
        // \think\facade\Cache::set(json_encode($map).'min',$count,3600*11.1);
        return $count;
    }

    //身高体重BMI
    public static function xueyuanBmi()
    {
        /* if(cache('xueyuanBmi')){
             $list = cache('xueyuanBmi');
             return json_decode($list,true);
         }*/
        if (getParam('high_weight')) {
            $list = Departments::where('mark', 1)
                ->where('id', getParam('high_weight'))
                ->cache(true, 130)
                ->select()
                ->toArray();
        } else {
            $list = self::departSelect();
        }
        $list1 = array_column($list, 'name');
        $list2 = [];
        $list3 = [];

        $field = ['itemCode'];
        foreach ($list as $v) {
            $field[] = 'AVG(IF('.getDepartField($v['id']).' = '.$v['id'].', score, null)) AS avg_' . $v['id'];
        }
        $scoreData = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw(implode(',', $field))
            ->whereIn('itemCode', ['weight', 'height'])
            ->group('itemCode')
            ->select()
            ->toArray();
        $scoreData = array_column($scoreData, null, 'itemCode');

        foreach ($list as $v) {
//            $map1   = [];
//            $map1[] = [getDepartField($v['id']), '=', $v['id']];
//            $map1[] = ['itemCode', '=', 'weight'];
//            $map1   = array_merge_recursive($map1, whereSchoolMap(false));
//
//            $weight = SportRecord::where($map1)->cache(260)->avg('score');//体重
//            $map2   = [];
//            $map2[] = [getDepartField($v['id']), '=', $v['id']];
//            $map2[] = ['itemCode', '=', 'height'];
//            $map2   = array_merge_recursive($map2, whereSchoolMap(false));
//            $height = SportRecord::where($map2)->cache(280)->avg('score');//体重

            $list2[] = (int)($scoreData['weight']['avg_' . $v['id']] ?? 0);
            $list3[] = (int)($scoreData['height']['avg_' . $v['id']] ?? 0);
        }
        return [$list1, $list2, $list3];

        //\think\facade\Cache::set('xueyuanBmi',json_encode($list),3600*10.9);
    }

    //学院列表
    public static function departSelect()
    {
        $where = [];
        $where[] = ['mark','=',1];
        if(getParam('type') == 1){
            $where[] = ['type','=',1];
        }
        if(getParam('type') == 2){
            $where[] = ['type','=',2];
        }
        $list = Departments::where($where)
            //->cache(true,3600*10)
            ->select()
            ->toArray();
        return $list;
    }
    //各院系项目等级
    public static function projectLevel($lmap = '')
    {
        $list     = self::departSelect();
        $new_list = [];
        $post     = input();
        $list1    = array_column($list, 'name');
        $list2    = [];
        $list3    = [];
        $list4    = [];
        $list5    = [];

        $ratingDataTemp = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw("rating, departmentsId, COUNT(DISTINCT memberId) count")
            ->group('rating, departmentsId')
            ->whereIn('rating', [0, 1, 2, 3])
            ->whereIn('departmentsId', array_column($list, 'id'))
            ->select()
            ->toArray();
        $ratingData = [];
        foreach ($ratingDataTemp as $val) {
            $ratingData[$val['departmentsId']][$val['rating']] = $val['count'];
        }
        $superior = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw("rating, COUNT(DISTINCT memberId) count")
            ->group('rating')
            ->whereIn('rating', [0, 1, 2, 3])
            ->where('tiyu_superior', 21)
            ->select()
            ->toArray();
        $ratingData[21] = array_column($superior, 'count', 'rating');

        foreach ($list as $v) {
//            $rmap   = [];
//            $rmap[] = [getDepartField($v['id']), '=', $v['id']];
//
//            $rmap = array_merge_recursive($rmap, whereSchoolMap(false));
//            if ($lmap) {
//                $rmap = array_merge_recursive($rmap, $lmap);
//            }
//            $goodNum   = self::countRaningNum($rmap, 1);
//            $liangNum  = self::countRaningNum($rmap, 2);
//            $jigeNum   = self::countRaningNum($rmap, 3);
//            $noJigeNum = self::countRaningNum($rmap, 0);

            $goodNum   = $ratingData[$v['id']][1] ?? 0;
            $liangNum  = $ratingData[$v['id']][2] ?? 0;
            $jigeNum   = $ratingData[$v['id']][3] ?? 0;
            $noJigeNum = $ratingData[$v['id']][0] ?? 0;
            $total     = $goodNum + $liangNum + $jigeNum + $noJigeNum;

            $goodRate  = cacl_rate($goodNum, $total);
            $liangRate = cacl_rate($liangNum, $total);
            $jigeRate  = cacl_rate($jigeNum, $total);
            $noJigeNum = cacl_rate($noJigeNum, $total);

            $list2[] = (int)$goodRate;
            $list3[] = (int)$liangRate;
            $list4[] = (int)$jigeRate;
            $list5[] = (int)$noJigeNum;
        }
        array_unshift($list1, 'product');
        array_unshift($list2, '优秀');
        array_unshift($list3, '良好');
        array_unshift($list4, '合格');
        array_unshift($list5, '不合格');
        $list = [
            $list1,
            $list2,
            $list3,
            $list4,
            $list5
        ];

        return $list;
    }

    public static function countRaningNum($rmap,$ranting=0)
    {
        /* if(cache(json_encode($rmap).'countRaningNum')){
             return cache(json_encode($rmap));
         }*/
        //**
        $count = SportRecord::where($rmap)->field('id')
            ->where('rating',$ranting)->cache(3600*8)
            //->cache(true,3600)
            //->group('memberId')
            ->count();
        //\think\facade\Cache::set(json_encode($rmap).'countRaningNum',$count,3600*12.5);
        return $count;
    }

    //各项目平均分于等级
    //1=男生2=女生
    public static function levelAvg($gender = 1)
    {
        $list = self::projectSelect();
        $list1 = array_column($list,'name');
        $new_list = [];
        $list2 = [];
        $list3 = [];
        $list4 = [];

        foreach($list as $v){
            $rmap = [];
            $rmap[] = ['projectId','=',$v['id']];
            $rmap[] = ['gender',"=",$gender];
            $rmap = array_merge_recursive($rmap,whereSchoolMap(false));
            if(getParam('man_level') && $gender == 1){
                $rmap[] = [getDepartField(getParam('man_level')),'=',getParam('man_level')];
            }
            if(getParam('woman_level') && $gender == 2){
                $rmap[] = [getDepartField(getParam('woman_level')),'=',getParam('woman_level')];
            }
            $goodNum = self::countRaningNum($rmap,1);
            $liangNum = self::countRaningNum($rmap,2);
            $jigeNum = self::countRaningNum($rmap,3);
            $noJigeNum = self::countRaningNum($rmap,0);

            $total = $goodNum+$liangNum+$jigeNum+$noJigeNum;

            $goodRate = cacl_rate($goodNum+$liangNum,$total);
            $hegeRate = cacl_rate($goodNum+$liangNum+$jigeNum,$total);
            $avg = SportRecord::where($rmap)->whereNotNull('pingscore')->cache(180)->avg('pingscore');

            $list2[] = pro_float($avg);
            $list3[] = pro_float($goodRate);
            $list4[] = pro_float($hegeRate);


        }
        $data = [$list2,$list3,$list4];
        return [$list1,$data];
    }

    public static function levelAvg2()
    {
        $list = self::projectSelect();
        $list1 = array_column($list,'name');
        $new_list = [];


        $genderWhere = [1 => 'gender = 1', 2 => 'gender = 2'];
        if(getParam('man_level')){
            $genderWhere[1] .= ' and ' . getDepartField(getParam('man_level')) . ' = ' . getParam('man_level');
        }
        if(getParam('woman_level')){
            $genderWhere[2] .= ' and ' . getDepartField(getParam('woman_level')) . ' = ' . getParam('woman_level');
        }
        $ratingDataTemp = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw("rating, projectId, COUNT(DISTINCT IF({$genderWhere[1]}, memberId, null)) AS count_1, COUNT(DISTINCT IF({$genderWhere[2]}, memberId, null)) AS count_2")
            ->group('rating, projectId')
            ->whereIn('rating', [0, 1, 2, 3])
            ->whereIn('projectId', array_column($list, 'id'))
            ->whereIn('gender', [1, 2])
            ->select()
            ->toArray();
        $ratingData = [];
        foreach ($ratingDataTemp as $val) {
            $ratingData[$val['projectId']][$val['rating']][1] = $val['count_1'];
            $ratingData[$val['projectId']][$val['rating']][2] = $val['count_2'];
        }
        $avgData = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw("projectId, AVG(IF({$genderWhere[1]}, pingscore, null)) AS avg_1, AVG(IF({$genderWhere[2]}, pingscore, null)) AS avg_2")
            ->group('projectId')
            ->whereIn('projectId', array_column($list, 'id'))
            ->whereIn('gender', [1, 2])
            ->select()
            ->toArray();
        $avgData = array_column($avgData, null, 'projectId');

        foreach ([1, 2] as $gender) {
            $list2 = [];
            $list3 = [];
            $list4 = [];
            foreach ($list as $v) {
                $goodNum   = $ratingData[$v['id']][1][$gender] ?? 0;
                $liangNum  = $ratingData[$v['id']][2][$gender] ?? 0;
                $jigeNum   = $ratingData[$v['id']][3][$gender] ?? 0;
                $noJigeNum = $ratingData[$v['id']][0][$gender] ?? 0;
                $total     = $goodNum + $liangNum + $jigeNum + $noJigeNum;

                $goodRate = cacl_rate($goodNum + $liangNum, $total);
                $hegeRate = cacl_rate($goodNum + $liangNum + $jigeNum, $total);
                //$avg      = SportRecord::where($rmap)->whereNotNull('pingscore')->cache(180)->avg('pingscore');
                $avg      = $avgData[$v['id']]['avg_' . $gender] ?? 0;

                $list2[] = pro_float($avg);
                $list3[] = pro_float($goodRate);
                $list4[] = pro_float($hegeRate);
            }
            $data[$gender] = [$list2, $list3, $list4];
        }

        return ['list' => $list1, 'data' => $data];
    }

    public static function levelbanjiAvg($id,$gender = 1)
    {
        $list = self::projectSelect();
        $list1 = array_column($list,'name');
        $new_list = [];
        $list2 = [];
        $list3 = [];
        $list4 = [];

        foreach($list as $v){
            $rmap = [];
            $rmap[] = ['projectId','=',$v['id']];
            $rmap[] = ['gender',"=",$gender];
            if ($id) {
                $rmap[] = ['classId',"=",$id];
            }

            $rmap = array_merge_recursive($rmap,whereSchoolMap(false));
            $goodNum = self::countRaningNum($rmap,1);
            $liangNum = self::countRaningNum($rmap,2);
            $jigeNum = self::countRaningNum($rmap,3);
            $noJigeNum = self::countRaningNum($rmap,0);

            $total = $goodNum+$liangNum+$jigeNum+$noJigeNum;

            $goodRate = cacl_rate($goodNum+$liangNum,$total);
            $hegeRate = cacl_rate($goodNum+$liangNum+$jigeNum,$total);
            $avg = SportRecord::where($rmap)->whereNotNull('pingscore')->avg('pingscore');

            $list2[] = pro_float($avg);
            $list3[] = pro_float($goodRate);
            $list4[] = pro_float($hegeRate);


        }
        $data = [$list2,$list3,$list4];
        return [$list1,$data];
    }

    public static function yuanxiAvg($lmap = '')
    {
        $list     = self::departSelect();
        $new_list = [];
        $list1    = array_column($list, 'name');
        $list2    = [];
        $list3    = [];
        $list4    = [];

        $map         = whereSchoolMap(false);
        $avgData     = SportRecord::where($map)
            ->fieldRaw(
                'departmentsId, avg(pingscore) avg, avg(if(gender=1,pingscore,NULL)) avg_1, avg(if(gender=2,pingscore,NULL)) avg_2'
            )
            ->whereIn('departmentsId', array_column($list, 'id'))
            ->whereIn('gender', [1, 2])
            ->group('departmentsId')
            ->select()
            ->toArray();
        $avgData     = array_column($avgData, null, 'departmentsId');
        $avgData[21] = SportRecord::where($map)
            ->fieldRaw(
                'avg(pingscore) avg, avg(if(gender=1,pingscore,NULL)) avg_1, avg(if(gender=2,pingscore,NULL)) avg_2'
            )
            ->where('tiyu_superior', 21)
            ->whereIn('gender', [1, 2])
            ->find()
            ->toArray();

        foreach ($list as $v) {
//            $map   = [];
//            $map[] = ['gender', '=', 1];
//            $map[] = [getDepartField($v['id']), '=', $v['id']];
//            $map   = array_merge_recursive($map, whereSchoolMap(false));
//            if ($lmap) {
//                $map = array_merge_recursive($map, $lmap);
//            }
//            $s1 = (int)SportRecord::where($map)->cache(400)->avg('pingscore');
//
//            $wmap   = [];
//            $wmap[] = ['gender', '=', 2];
//            $wmap[] = [getDepartField($v['id']), '=', $v['id']];
//            $wmap   = array_merge_recursive($wmap, whereSchoolMap(false));
//            if ($lmap) {
//                $wmap = array_merge_recursive($wmap, $lmap);
//            }
//            $s2 = (int)SportRecord::where($wmap)->cache(420)->avg('pingscore');
//
//            $amap   = [];
//            $amap[] = [getDepartField($v['id']), '=', $v['id']];
//            $amap   = array_merge_recursive($amap, whereSchoolMap(false));
//            if ($lmap) {
//                $amap = array_merge_recursive($amap, $lmap);
//            }
//            $avg = SportRecord::where($amap)->cache(430)->avg('pingscore');

            $list2[] = pro_float($avgData[$v['id']]['avg_1'] ?? 0);
            $list3[] = pro_float($avgData[$v['id']]['avg_2'] ?? 0);
            $list4[] = pro_float($avgData[$v['id']]['avg'] ?? 0);
        }
        array_unshift($list1, 'product');
        array_unshift($list2, '男生');
        array_unshift($list3, '女生');
        array_unshift($list4, '平均分');
        $list = [
            $list1,
            $list2,
            $list3,
            $list4
        ];

        return $list;
    }

    public static function projectSelect()
    {
        $list = Project::where("type",0)
            ->cache(3600*10)
            ->select()
            ->toArray();
        return $list;
    }

    public static function ydshichang()
    {
        $list = self::projectSelect();

        $new_list = [];

        $map = whereSchoolMap(false);
        if (getParam('yundongshichang')) {
            $map[] = [getDepartField(getParam('yundongshichang')), '=', getParam('yundongshichang')];
        }
        $countTemp = SportRecord::where($map)
            ->fieldRaw('projectId, gender, sum(duration) duration, sum(IF(unit = "秒", score, NULL)) score')
            ->whereIn('projectId', array_column($list, 'id'))
            ->whereIn('gender', [1, 2])
            ->group('projectId, gender')
            ->select()
            ->toArray();
        $count = [];
        foreach ($countTemp as $val) {
            $count[$val['projectId']][$val['gender']] = pro_float($val['duration']) + pro_float($val['score']);
        }
        //参与率计算
        foreach ($list as $k => $v) {
            $new_list[$k]['time'] = $v['name'];
//            $map                  = [];
//
//            $map[] = ['gender', '=', 1];
//            $map[] = ['projectId', '=', $v['id']];
//            if (getParam('yundongshichang')) {
//                //$map += ['departmentsId'=>getParam('yundongshichang')];
//                $map[] = [getDepartField(getParam('yundongshichang')), '=', getParam('yundongshichang')];
//            }
//            $map        = array_merge_recursive($map, whereSchoolMap(false));
//
//            $man_count1 = SportRecord::where($map)->cache(true, 310)->whereNotNull('duration')->sum('duration');
//            $man_count2 = SportRecord::where($map)->cache(true, 280)->where('unit', '秒')->sum('score');
//            $man_count  = pro_float($man_count1) + pro_float($man_count2);
//
//            $map2   = [];
//            $map2[] = ['gender', '=', 2];
//            $map2[] = ['projectId', '=', $v['id']];
//            if (getParam('yundongshichang')) {
//                $map2[] = [getDepartField(getParam('yundongshichang')), '=', getParam('yundongshichang')];
//            }
//            $map2                   = array_merge_recursive($map2, whereSchoolMap(false));
//            $woman_count1           = SportRecord::where($map2)->cache(true, 390)->whereNotNull('duration')->sum(
//                'duration'
//            );
//            $woman_count2           = SportRecord::where($map2)->cache(true, 300)->where('unit', '秒')->sum('score');
//            $woman_count            = pro_float($woman_count1) + pro_float($woman_count2);

            $new_list[$k]['views']  = pro_float($count[$v['id']][1] ?? 0);
            $new_list[$k]['visits'] = pro_float($count[$v['id']][2] ?? 0);
        }

        return $new_list;
    }

    public static function banjiydshichang($id)
    {
        $list = self::projectSelect();

        $new_list = [];
        //参与率计算
        foreach($list as $k=>$v){
            $new_list[$k]['time'] = $v['name'];
            $map = [];

            $map[] = ['gender', '=', 1];
            $map[] = ['projectId', '=', $v['id']];
            $map[] = ['classId', '=', $id];
            if(getParam('yundongshichang')){
                $map[] = ['departmentsId','=',getParam('yundongshichang')];
            }
            $map = array_merge_recursive($map,whereSchoolMap(false));
            $man_count1 = SportRecord::where($map)->whereNotNull('duration')->sum('duration');
            $man_count2 = SportRecord::where($map)->where('unit','秒')->sum('score');
            $man_count = $man_count1+$man_count2;

            $map2 = [];
            $map2[] = ['gender', '=', 2];
            $map2[] = ['projectId', '=', $v['id']];
            $map2[] = ['classId', '=', $id];
            if(getParam('yundongshichang')){
                $map2[] = ['departmentsId','=',getParam('yundongshichang')];
            }
            $map2 = array_merge_recursive($map2,whereSchoolMap(false));
            $woman_count1 =  SportRecord::where($map2)->whereNotNull('duration')->sum('duration');
            $woman_count2 = SportRecord::where($map2)->where('unit','秒')->sum('score');
            $woman_count = $woman_count1+$woman_count2;
            $new_list[$k]['views'] = $man_count+0;
            $new_list[$k]['visits'] = $woman_count+0;
        }
        //print_r($new_list);die();
        return $new_list;
    }

    public static function projectArrList($map = '')
    {
        $list = self::projectSelect($map);
        //print_r($list);
        //项目人数/参与人数 = 参与率
        $total = self::ticeren();//参与体测总人数
        $rate  = [];
        //参与率计算
//        foreach ($list as $v) {
//            if ($total) {
//                $smap   = [];
//                $smap[] = ['projectId', '=', $v['id']];
//                if (getParam('canyulv')) {
//                    $smap[] = [getDepartField(getParam('canyulv')), '=', getParam('canyulv')];
//                }
//                if (!empty($map)) {
//                    $smap = array_merge_recursive($smap, $map);
//                }
//                $smap    = array_merge_recursive($smap, whereSchoolMap(false));
//                print_r($smap);
//
//                $pro_num = self::ticeren($smap);
////                $count = SportRecord::where($map)->field("id")
////                    ->group('memberId')
////                    //->cache('gmemgroup',250)
////                    ->count();
//
//                $res     = cacl_rate($pro_num, $total);
//            } else {
//                $res = 0;
//            }
//            $rate[] = $res;
//        }
        if ($total) {
            $count = SportRecord::where($map)
                ->fieldRaw('projectId,COUNT(DISTINCT memberId) AS count')
                ->whereIn('projectId', array_column($list, 'id'))
                ->group('projectId')
                ->select()
                ->toArray();
            $count = array_column($count, 'count', 'projectId');
        }
        foreach ($list as $v) {
            $rate[] = isset($count[$v['id']]) && $total ? cacl_rate($count[$v['id']], $total) : 0;
        }
        $list = array_column($list, 'name');

        return [$list, $rate];
    }

    public static function ranting($map,$raning)
    {
        //return 0;
        /*if(cache(json_encode($map).$raning)){
            return cache(json_encode($map).$raning);
        }*/
        $map[] = ['rating' ,'=',$raning];
        $c1 = SportRecord::where($map)->field('id,rating')
            ->cache(360*10)
            //->whereNotNull('pingscore')
            ->count();//不及格
        //\think\facade\Cache::set(json_encode($map).$raning,$c1);
        return $c1;
    }

    public static function xueyuanArrList()
    {

        $list = self::departSelect();

        $new_list = [];
        $hegelv   = [];

        $data1 = [];
        $data2 = [];
        $data3 = [];
        $data4 = [];

        $field = ['rating', 'departmentsId'];
        foreach ($list as $v) {
            $field[] = 'COUNT(IF('.getDepartField($v['id']).' = '.$v['id'].', id, null)) AS count_' . $v['id'];
        }
        $ratingDataTemp = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw('rating, departmentsId, count(id) count')
            ->whereIn('rating', [0, 1, 2, 3])
            ->group('rating, departmentsId')
            ->select()
            ->toArray();
        $ratingData = [];
        foreach ($ratingDataTemp as $val) {
            $ratingData[$val['departmentsId']][$val['rating']] = $val['count'];
        }
        $superiorData = SportRecord::where(whereSchoolMap(false))
            ->fieldRaw('rating, count(id) count')
            ->where('tiyu_superior', 21)
            ->whereIn('rating', [0, 1, 2, 3])
            ->group('rating')
            ->select()
            ->toArray();
        $ratingData[21] = array_column($superiorData, 'count', 'rating');

        foreach ($list as $v) {
            $new_list[] = $v['name'];
//            $map        = [];
//            $map[]      = [getDepartField($v['id']), '=', $v['id']];
//            $map        = array_merge_recursive($map, whereSchoolMap(false));
//            $c1         = self::ranting($map, 0);//不及格
//            $c2         = self::ranting($map, 1);//优秀
//            $c3         = self::ranting($map, 2);//良好
//            $c4         = self::ranting($map, 3);//及格
            $c1         = $ratingData[$v['id']][0] ?? 0;//不及格
            $c2         = $ratingData[$v['id']][1] ?? 0;//优秀
            $c3         = $ratingData[$v['id']][2] ?? 0;//良好
            $c4         = $ratingData[$v['id']][3] ?? 0;//及格

            $sum        = $c1 + $c2 + $c3 + $c4;
            //echo $c1.'-'.$c2.'-'.$c3.'-'.$c4.'-'.$sum.PHP_EOL;
            $data1[] = cacl_rate($c1, $sum) . '%';
            $data2[] = cacl_rate($c2, $sum) . '%';
            $data3[] = cacl_rate($c3, $sum) . '%';
            $data4[] = cacl_rate($c4, $sum) . '%';
        }
        $rate_list = [
            [
                'name'     => '优秀',
                'type'     => 'bar',
                'stack'    => 'Search Engine',
                'emphasis' => ['focus', 'series'],
                'label'    => 'this.labelOption',
                'data'     => $data1
            ],
            [
                'name'     => '良好',
                'type'     => 'bar',
                'stack'    => 'Search Engine',
                'emphasis' => ['focus', 'series'],
                'label'    => 'this.labelOption',
                'data'     => $data2
            ],
            [
                'name'     => '合格',
                'type'     => 'bar',
                'stack'    => 'Search Engine',
                'emphasis' => ['focus', 'series'],
                'label'    => 'this.labelOption',
                'data'     => $data3
            ],
            [
                'name'     => '不合格',
                'type'     => 'bar',
                'stack'    => 'Search Engine',
                'emphasis' => ['focus', 'series'],
                'label'    => 'this.labelOption',
                'data'     => $data4
            ],
        ];

        //print_R($new_list);die();
        return [$new_list, $rate_list];
    }

    //专业
    public static function profArrList($departId)
    {
        $list = self::getProfListById($departId);
        $new_list =[];
        $hegelv = [];

        $data1 = [];
        $data2 = [];
        $data3 = [];
        $data4 = [];

        foreach($list as $v){
            $new_list[]=$v['name'];
            $map = [];
            $map[] = ['professionalId','=',$v['id']];
            $map += whereSchoolMap(false);

            $c1 = self::ranting($map,0);//不及格
            $c2 =self::ranting($map,1);//优秀
            $c3 = self::ranting($map,2);//良好
            $c4 = self::ranting($map,3);//及格
            $sum = $c1+$c2+$c3+$c4;
            //echo $c1.'-'.$c2.'-'.$c3.'-'.$c4.'-'.$sum.PHP_EOL;
            $data1[] = cacl_rate($c1,$sum).'%';
            $data2[] = cacl_rate($c2,$sum).'%';
            $data3[] = cacl_rate($c3,$sum).'%';
            $data4[] = cacl_rate($c4,$sum).'%';


        }
        $rate_list =  [
            [
                'name' => '优秀',
                'type'=>'bar',
                'stack' =>'Search Engine',
                'emphasis'=>['focus','series'],
                'label'=>'this.labelOption',
                'data'=> $data1
            ],
            [
                'name' => '良好',
                'type'=>'bar',
                'stack' =>'Search Engine',
                'emphasis'=>['focus','series'],
                'label'=>'this.labelOption',
                'data'=> $data2
            ],
            [
                'name' => '合格',
                'type'=>'bar',
                'stack' =>'Search Engine',
                'emphasis'=>['focus','series'],
                'label'=>'this.labelOption',
                'data'=> $data3
            ],
            [
                'name' => '不合格',
                'type'=>'bar',
                'stack' =>'Search Engine',
                'emphasis'=>['focus','series'],
                'label'=>'this.labelOption',
                'data'=> $data4
            ],
        ];
        //print_R($new_list);die();
        return [$new_list,$rate_list];
    }


    public static function xueyuan()
    {
        $list = self::departSelect();
        $new_list = [];

        $map = whereSchoolMap(false);
        $ticeren = SportRecord::where($map)
            ->fieldRaw('departmentsId, COUNT(DISTINCT memberId) AS total')
            ->whereIn('departmentsId', array_column($list, 'id'))
            ->group('departmentsId')
            ->select()
            ->toArray();
        $ticeren = array_column($ticeren, 'total', 'departmentsId');
        $ticeren[21] = SportRecord::where($map)->where('tiyu_superior', 21)->count('memberId');
        $membCount = Memb::whereIn('superior', array_column($list, 'id'))
            ->fieldRaw('superior, count(id) total')
            ->group('superior')
            ->select()
            ->toArray();
        $membCount = array_column($membCount, 'total', 'superior');
        $membCount['21'] =  Memb::where('tiyu_superior', 21)->count('id');
        foreach ($list as $k=>$v){
            $new_list[$k]['time'] = $v['name'];

            //参与人数
            $visits_num = $ticeren[$v['id']] ?? 0;
            $new_list[$k]['visits'] = $visits_num;

            $count = $membCount[$v['id']] ?? 0;
            $views = 0;
            if($count > 0){
                $views = round($visits_num/$count*100,2);
            }
            //参与率
            $new_list[$k]['views'] = $views;
        }
        return $new_list;
        //foreach($list as ){}
    }

    public static function getProfListById($departId)
    {
        $list = Prof::where('mark',1)
            ->where('superior',$departId)
            ->cache(3600*6)
            ->select()
            ->toArray();
        return $list;
    }

    public static function departxueyuan($departId)
    {
        $list = self::getProfListById($departId);
        $new_list = [];
        foreach ($list as $k=>$v){
            $new_list[$k]['time'] = $v['name'];
            $map = [];
            $map[] = [getProfField($v['id']) ,'=', $v['id']];
            $map = array_merge_recursive($map,whereSchoolMap(false));
            //参与人数
            $visits_num = self::ticeren($map);

            $new_list[$k]['visits'] = self::ticeren($map);

            $count = Memb::where('professional', $v['id'])->count();
            $views = 0;
            if($count > 0){
                $views = round($visits_num/$count,2)*100;
            }
            //参与率
            $new_list[$k]['views'] = $views;
        }
        //print_R($new_list);
        return $new_list;
        //foreach($list as ){}
    }

    public static function profXueyuan($departId)
    {
        $list = Clss::where('professional',$departId)
            ->where('mark',1)
            ->select()
            ->toArray();
        $new_list = [];
        foreach ($list as $k=>$v){
            $new_list[$k]['time'] = $v['name'];
            $map = [];
            $map[] = [getClassField(input('departmentsId')),'=',$v['id']];
            $map = array_merge_recursive($map,whereSchoolMap(false));
            //参与人数
            $visits_num = self::ticeren($map);
            $new_list[$k]['visits'] = $visits_num;

            $count = Memb::where('class', $v['id'])->count();
            $views = 0;
            if($count > 0){
                $views = cacl_rate($visits_num,$count);
            }
            //参与率
            $new_list[$k]['views'] = $views;
        }
        //print_R($new_list);
        return $new_list;
        //foreach($list as ){}
    }

    public static function yundongRate($id)
    {
        $map = [];
        if ($id) {
            $map[] = ['classId','=',$id];
        }

        $map = array_merge_recursive($map,whereSchoolMap(false));
        $yundong_num = DataModel::ticeren($map);
        $where = [];
        if ($id) {
            $where['class'] = $id;
        }
        $zong_num = \app\admin\model\Memb::where($where)->count();
        $yundong_rate = 0;
        if($zong_num>0){
            $yundong_rate = cacl_rate($yundong_num,$zong_num).'%';
        }
        $map2 = [];
        if ($id) {
            $map2[] = ['classId','=',$id];
        }

        $map2[] = ['gender','=',1];
        $map2 = array_merge_recursive($map2,whereSchoolMap(false));
        $man_num = DataModel::ticeren($map2);

        $map3 = [];
        if ($id) {
            $map3[] = ['classId','=',$id];
        }

        $map3[] = ['gender','=',2];
        $map3 = array_merge_recursive($map3,whereSchoolMap(false));
        $woman_rate = DataModel::ticeren($map3);
        //echo $yundong_rate;
        //die();
        $t = $man_num+$woman_rate;
        $man_rate = 0;
        $woman_rate = 0;
        if((int)$t > 0){
            $man_rate = cacl_rate($man_num,$t);
            $woman_rate = cacl_rate($woman_rate,$t);
        }

        $map4 = [];
        if ($id) {
            $map4[] = ['classId','=',$id];
        }

        $map4 = array_merge_recursive($map4,whereSchoolMap(false));
        $zong_time1 = SportRecord::where($map4)->sum('duration');
        $zong_time2 = SportRecord::where($map4)->where('unit','秒')->sum('score');
        $zong_time = $zong_time1+$zong_time2;
        return [$yundong_num,$zong_num,$yundong_rate,$man_rate,$woman_rate,$zong_time+0];
    }

    //各院系运动人数
    public static function yuanxihYundongNum($type=1)
    {
        if(getParam('yuanxi_avg')){
            $where = [];
            $where[] = ['mark','=',1];
            $where[] = ['id','=',getParam('yuanxi_avg')];
            $list = Departments::where($where)
                ->select()
                ->toArray();
        }else{
            $list = self::departSelect();
        }

        $new_list = [];
        $list1 = array_column($list,'name');
        $list2 = [];
        $list3 = [];
        $list4 = [];
        foreach($list as $v){
            $map1 = [];
            $map1[] = ['gender','=','1'];
            $map1[] = [getDepartField($v['id']),'=',$v['id']];
            $map1 = array_merge_recursive($map1,whereTimeMap($type)) ;
            $c1 = self::ticeren($map1);
            $map2 = [];
            $map2[] = ['gender','=','1'];
            $map2[] = [getDepartField($v['id']),'=',$v['id']];
            $map2 = array_merge_recursive($map2,whereTimeMap($type));
            $c2 = self::ticeren($map2);
            $list2[] = $c1;
            $list3[] = $c2;
            $list4[] = $c1+$c2;
        }
        array_unshift($list1,'product');
        array_unshift($list2,'男生');
        array_unshift($list3,'女生');
        array_unshift($list4,'总数');
        $list = [
            $list1, $list2,$list3,$list4
        ];
        return $list;
    }

    //各院系人均运动时长
    public static function ydsc($type = 1)
    {
        if(getParam('avg_grade')){
            $where[] = ['mark','=',1];
            $where[] = ['id','=',getParam('avg_grade')];
            $list = Departments::where($where)
                ->select()
                ->toArray();
        }else{
            $list = self::departSelect();
        }

        $new_list = [];
        $list1 = array_column($list,'name');
        $list2 = [];
        $list3 = [];
        $list4 = [];
        foreach($list as $v){
            $count = Memb::where(getMembSupField($v['id']),$v['id'])->count();
            $map = [];
            $map[] = [getDepartField($v['id']),'=',$v['id']];
            $map = array_merge_recursive($map,whereTimeMap($type));
            $time1 = SportRecord::where($map)->sum('duration');
            $time2 =  SportRecord::where($map)->where('unit','秒')->sum('score');
            //echo $time.'-'.$count.'-'.$v['id'].PHP_EOL;
            $time = $time1+$time2;
            $rate = 0;
            if($count > 0){
                //$rate = (int)(($time)/$count);
                $rate = cacl_rate($time,$count);
            }
            $list2[] = $rate;
            //$list3[] = 0;
            //$list4[] = 0;
        }
        array_unshift($list1,'product');
        array_unshift($list2,'人均运动时长');
        $list = [
            $list1,
            $list2,
        ];
        return $list;
    }

    //各运动成绩占比
    public static function ydcjZhanbi($type=1)
    {
        if(getParam('chengji_depart')){
            $list = Departments::where('id',getParam('chengji_depart'))
                ->select()
                ->toArray();
        }else{
            $list = self::departSelect();
        }
        $new_list = [];
        $list1 = array_column($list,'name');
        $list2 = [];
        $list3 = [];
        $list4 = [];
        foreach($list as $v){
            $rmap = [];
            $rmap[] = [getDepartField($v['id']),'=',$v['id']];
            $rmap = array_merge_recursive($rmap,whereTimeMap($type));
            $goodNum = self::countRaningNum($rmap,1);
            $liangNum = self::countRaningNum($rmap,2);
            $jigeNum = self::countRaningNum($rmap,3);
            $noJigeNum = self::countRaningNum($rmap,0);

            $total = $goodNum+$liangNum+$jigeNum+$noJigeNum;

            $goodRate = cacl_rate($goodNum+$liangNum,$total);
            $hegeRate = cacl_rate($goodNum+$liangNum+$jigeNum,$total);
            $avg = SportRecord::where($rmap)->whereNotNull('pingscore')->avg('pingscore');

            $list2[] = pro_float($avg);
            $list3[] = pro_float($goodRate);
            $list4[] = pro_float($hegeRate);
        }
        array_unshift($list1,'product');
        array_unshift($list2,'平均分');
        array_unshift($list3,'优秀率');
        array_unshift($list4,'合格率');
        $list = [
            $list1, $list2,$list3,$list4
        ];
        return $list;
    }
    //各运动占比
    public static function yundongZhanbi($type=1)
    {

        $list = self::projectSelect();
        $new_list = [];
        $list1 = array_column($list,'name');
        $list2 = [];
        $sum = 0;
        foreach($list as $v){
            //合格率、优良率
            $map = [];
            $map[]= ['projectId','=',$v['id']];
            if(getParam('zhanbi_depart')){
                $map[] = [getDepartField(getParam('zhanbi_depart')),'=',getParam('zhanbi_depart')];
            }
            $map = array_merge_recursive($map, whereTimeMap($type));
            $count1 = SportRecord::where($map)->field('id')->count();
            $sum += $count1;                                            ;
            //$list2[] = 0;
        }
        //echo $sum;die();
        foreach ($list as $v){
            //合格率、优良率
            $map = [];
            $map[]= ['projectId','=',$v['id']];
            if(getParam('zhanbi_depart')){
                $map[] = ['departmentsId','=',getParam('zhanbi_depart')];
            }
            $map = array_merge_recursive($map, whereTimeMap($type));
            $count1 = SportRecord::where($map)->field("*")->count();
            if($sum > 0){
                $list2[] = cacl_rate($count1,$sum);
            }else{
                $list2[] = 0;
            }
        }
        return [$list1,$list2];
    }

    public static function classRankTime($rmap)
    {
        $rmap = array_merge_recursive($rmap,whereSchoolMap(false));
        //总时长
        $list = SportRecord::where($rmap)
            ->field('sum(duration) as rank1,memberId,classId,id,testerName,testerNumber')
            ->group('memberId')
            ->order('rank1 desc')
            ->whereNotNull('duration')
            ->limit(10)
            ->select()
            ->toArray();
        //['id' => 12,'rank'=> 1,'no' => 20230340,'name' => '张小虎1','num' => '32.324',],
        $new = [];
        foreach($list as $k=>$v){
            $new[$k]['id'] = $v['memberId'];
            $new[$k]['rank'] = $k+1;
            $new[$k]['no'] = $v['testerNumber'];
            $new[$k]['name'] = $v['testerName'];
            $class = Clss::get($v['classId']);
            $new[$k]['depart'] = $class?$class['name']:'';
            $str = (int)pro_float($v['rank1']/60);
            if($str == 0){
                $num = $v['rank1'].'秒';
            }else{
                $num = $str.'分钟';
            }
            $new[$k]['num'] = $num;
        }
        //print_r($new);die();
        return $new;
    }

    public static function classRankDis($rmap)
    {
        $rmap = array_merge_recursive($rmap,whereSchoolMap(false));
        //总历程
        $list = SportRecord::where($rmap)
            ->field('sum(score) as rank1,classId,id,memberId,testerName,testerNumber')
            ->group('memberId')
            ->order('rank1 desc')
            ->whereNotNull('duration')
            ->limit(10)
            ->select()
            ->toArray();
        //['id' => 12,'rank'=> 1,'no' => 20230340,'name' => '张小虎1','num' => '32.324',],
        $new = [];
        foreach($list as $k=>$v){
            $new[$k]['id'] = $v['memberId'];
            $new[$k]['rank'] = $k+1;
            $new[$k]['no'] = $v['testerNumber'];
            $new[$k]['name'] = $v['testerName'];
            $class = Clss::get($v['classId']);
            $new[$k]['depart'] = $class?$class['name']:'';
            $new[$k]['num'] = $v['rank1'].'m';
        }
        //print_r($new);die();
        return $new;
    }
    //学生班级，年级排名
    public static function sutrank($where1,$where2)
    {
        $where1 = array_merge_recursive($where1,whereSchoolMap(false));
        $curr = SportRecord::where($where1)
            ->field('sum(pingscore) as rank1')
            ->group('memberId')
            ->find();
        // print_R($curr);die();
        $rank = 0;
        //总排名
        $where2 = array_merge_recursive($where2,whereSchoolMap(false));
        $list = SportRecord::where($where2)
            ->field('sum(pingscore) as rank1')
            ->group('memberId')
            ->select()
            ->toArray();
        $res = array_column($list,'rank1');
        if(!empty($curr['rank']) && $list){
            foreach ($list as $k=>&$v){
                $v['rank'] = $v['rank1'];
                if($v['rank1'] == $curr['rank1']){
                    $rank = $k+1;
                    break;
                }
            }
            //echo $rank;
        }else{
            if(!empty($list['rank'])){
                $rank = count($list);
            }
        }
        return $rank;
    }

    public static function stuBmi($map = [])
    {
        $monthArr1 = [];
        $h = [];
        for ($i=1;$i<=6;$i++){
            $m1=date('m',strtotime('-'.$i.'month'));
            $monthArr1[] = (int)$m1.'月';
            $t1 = date('Y-m-01',strtotime('-'.$i.'month'));
            $t2 = date('Y-m-d',strtotime($t1)+30*24*3600);
            $cmap = [];
            $cmap[] =  ['itemCode','=','weight'];
            $cmap[] = ['createdDate' ,[">",$t1],["<",$t2]];
            $cmap = array_merge_recursive($cmap,$map);
            $cmap = array_merge_recursive($cmap,whereSchoolMap(false));
            $v = SportRecord::where($cmap)->whereNotNull('bmi')->avg('bmi');
            //偏瘦 小于 17.9 正常17.9-24 超重24 - 28 肥胖>28
            if($v < 17.9 && $v>0){
                $msg = '偏瘦';
            }elseif($v>=17.9 && $v<=24){
                $msg = '正常';
            }elseif($v>24 && $v<= 28){
                $msg = '超重';
            }else{
                if($v == 0){
                    $msg = '暂无数据';
                }else{
                    $msg = '肥胖';
                }
            }
            $h[] =   ['value' => (int)$v,'name' => $msg];
        }
        $res = [$monthArr1,$h];
        //print_R($res);
        return $res;
    }

    public static function stuCanyu($wheremap)
    {
        $map = array_merge_recursive($wheremap,whereSchoolMap(false));
        $zong_num = SportRecord::where($map)->field('id')
            //->group('memberId')
            ->count('*');
        $zong_time = SportRecord::where($map)->sum('duration') ;
        $zong_avg = SportRecord::where($map)->avg('duration') ;

        $today = date('Y-m-d'); // 当前日期
        $sevenDaysAgo = date('Y-m-d', strtotime('-7 days')); // 7天前的日期

        $dateArray = array(); // 存储日期数组的变量

        for ($date = $sevenDaysAgo; $date <= $today; $date = date('Y-m-d', strtotime('+1 day', strtotime($date)))) {
            $dateArray[] = $date;
        }

        $arr = [];
        foreach($dateArray as $k=>$v){
            $arr[$k]['time'] = date('m-d',strtotime($v));
            $t1 = strtotime($v);
            $t2 = date('Y-m-d H:i:s', $t1+24*3600);
            $rmap = [];
            $rmap[] = ['createdDate' ,['>',$v],['<',$t2]];
            $rmap = array_merge_recursive($rmap,$wheremap);
            //**
            $v1 = SportRecord::where($rmap)->field("id")
                //->group('memberId')
                ->count('*');
            $v2 =  SportRecord::where($rmap)->sum('duration') ;
            $arr[$k]['visits'] =   (int)$v1;
            $arr[$k]['views'] = (int)$v2;
        }
        $data = [
            (int)$zong_num,(int)$zong_time,(int)$zong_avg,$arr
        ];
        //print_R($data);die();
        return $data;
    }
    //各院系项目等级
    public static function projectLevel2($memb)
    {
        $list = self::projectSelect();
        $new_list = [];
        $post = input();
        $list1 = array_column($list,'name');
        $list2 = [];
        $list3 = [];
        $list4 = [];
        foreach($list as $v){
            $rmap = [];
            $rmap[] = ['projectId','=',$v['id']];
            $rmap = array_merge_recursive($rmap,whereSchoolMap(false));

            $amap = [];
            $amap[] = ['memberId' ,'=',$memb['id']];
            $amap = array_merge_recursive($amap,$rmap);

            $avg = (int)SportRecord::where($amap)->whereNotNull('pingscore')->avg('pingscore');
            $gmap = [];
            $gmap[] = ['classId' ,'=',$memb['class']];
            $gmap = array_merge_recursive($gmap,$rmap);
            $gavg = (int)SportRecord::where($gmap)->whereNotNull('pingscore')->avg('pingscore');

            $max = SportRecord::where($gmap)->max('pingscore');
            $list2[] = $avg;
            $list3[] = $gavg;
            $list4[] = $max;
        }
        array_unshift($list1,'product');
        array_unshift($list2,'个人平均分');
        array_unshift($list3,'班级平均分');
        array_unshift($list4,'班级最高分');
        $list = [
            $list1, $list2,$list3,$list4
        ];
        //print_r($list);
        return $list;
    }

    public static function countBmi($map,$gender = '')
    {
        $map = array_merge_recursive($map,whereSchoolMap(false));
        if($gender){
            $map[] = ['gender' , '=' , $gender];
        }
        if(getParam('high_weight')){
            //根据表格结果需要切换筛选字段
            $map[] = ['departmentsId', '=',getParam('high_weight')];
        }
        return 0;
        //**
        $count = SportRecord::where($map)->field("id")
            //->group('memberId')
            ->cache(true,3600*2)
            //->whereNull('bmi')
            ->count();
        return $count;
    }
    public static function selectBmi($gender = '')
    {
        $row = Standard::where('name','BMI指数')->cache(true,110)->find();
        if($row){
            /* $sta = $row['standard'];
             print_r($sta);*/
            //男 偏瘦 小于 17.9 正常17.9-24 超重24 - 28 肥胖>28
            $ps = self::countBmi([['bmi','<',17.9]],$gender);
            $zc = self::countBmi([['bmi',['>=',17.9],['<=',24]]],$gender);
            $cz = self::countBmi([['bmi',['>',24],['<=',28]]],$gender);
            $fp = self::countBmi([['bmi','>',28]],$gender);

            // 女 偏瘦 小于17.9 正常17.9-24 超重24 - 28  肥胖>28
            $total = $ps+$zc+$cz+$fp;
            //echo $total;
            $rate1 = 0;
            $rate2 = 0;
            $rate3 = 0;
            $rate4 = 0;
            if($total>0){
                $rate1 = cacl_rate($ps,$total);
                $rate2 = cacl_rate($zc,$total);
                $rate3 = cacl_rate($cz,$total);
                $rate4 = cacl_rate($fp,$total);
            }
            $res = [
                [ 'value'=> $rate1, 'name'=>'偏瘦',],
                [ 'value'=> $rate2, 'name'=>'正常',],
                [ 'value'=> $rate3, 'name'=>'过量',],
                [ 'value'=> $rate4, 'name'=>'肥胖',]
            ];
            return $res;
        }else{
            $res = [
                [ 'value'=> 0, 'name'=>'偏瘦',],
                [ 'value'=> 0, 'name'=>'正常',],
                [ 'value'=> 0, 'name'=>'过量',],
                [ 'value'=> 0, 'name'=>'肥胖',]
            ];
            return $res;
        }

    }
}
